﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: November 17 2015
-- Description:	Receives two ids and a relationship name via a temp table and attempts to create or modify the relationship.
-- =============================================
CREATE PROCEDURE [dbo].[WriteDataToDB_EntityRelationships] 
	-- Add the parameters for the stored procedure here
	@RequestorEntityID int,
	@RequestedAction nvarchar(10) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Entity1ID int, @Entity2ID int, @ShortRelationshipName nvarchar(12)
	DECLARE @StartDate datetime, @EndDate datetime
	DECLARE @ErrorString nvarchar(255)
	DECLARE @Entity1Type nvarchar(100),@Entity2Type nvarchar(100)
	DECLARE @DeleteOverride nvarchar(20),@JunkRequest int
	DECLARE @OldStartDate DateTime,@OldEndDate DateTime


	SELECT @Entity1ID = StringValue FROM #JSONTable WHERE [NAME] = 'Entity1ID' AND StringValue <> 'null'
	SELECT @Entity2ID = StringValue FROM #JSONTable WHERE [NAME] = 'Entity2ID' AND StringValue <> 'null'
	SELECT @ShortRelationshipName = StringValue FROM #JSONTable WHERE [NAME] = 'ShortRelationshipName' AND StringValue <> 'null'
	--SELECT @OldStartDate = StringValue FROM #JSONTable WHERE [NAME] = 'OldStartDate' AND StringValue <> 'null'
	--SELECT @OldEndDate = StringValue FROM #JSONTable WHERE [NAME] = 'OldEndDate' AND StringValue <> 'null'
	SELECT @StartDate = CASE WHEN CHARINDEX('.00',StringValue) <> 0 THEN SUBSTRING(StringValue,1,CHARINDEX('.00',StringValue)-1) ELSE StringValue END
		FROM #JSONTable WHERE [NAME] = 'StartDate' AND StringValue <> 'null'
	SELECT @EndDate = CASE WHEN CHARINDEX('.00',StringValue) <> 0 THEN SUBSTRING(StringValue,1,CHARINDEX('.00',StringValue)-1) ELSE StringValue END
		FROM #JSONTable WHERE [NAME] = 'EndDate' AND StringValue <> 'null'
	SELECT @DeleteOverride = StringValue FROM #JSONTable WHERE [NAME] = 'DeleteOverride' AND StringValue <> 'null'
	IF @RequestedAction IS NULL
		SELECT @RequestedAction = StringValue FROM #JSONTable WHERE [NAME] = 'RequestedAction' AND StringValue <> 'null'
	-- if the requested action is still null...
	IF @RequestedAction IS NULL BEGIN
		SELECT @JunkRequest = Entity1ID 
		FROM EntityRelationships
		WHERE Entity1ID = @Entity1ID AND Entity2ID = @Entity2ID AND ShortRelationshipName = @ShortRelationshipName AND
			((@StartDate IS NULL OR @StartDate = StartDate) OR (@EndDate IS NULL OR @EndDate = EndDate))
		IF @@ROWCOUNT <> 0
			-- This relationship exists, so update
			SET @RequestedAction = 'Update'
		ELSE
			SET @RequestedAction = 'Create'
	END

	IF @Entity1ID IS NULL AND NOT @Entity2ID IS NULL
		SELECT @Entity1ID = StringValue FROM #JSONTable WHERE [NAME] = 'EntityID' AND StringValue <> 'null'
	IF @Entity2ID IS NULL AND NOT @Entity1ID IS NULL
		SELECT @Entity2ID = StringValue FROM #JSONTable WHERE [NAME] = 'EntityID' AND StringValue <> 'null'
	-- If any of the required fields are null, stop.
	IF @Entity2ID IS NULL OR @Entity1ID IS NULL OR @ShortRelationshipName IS NULL
		OR @RequestedAction IS NULL OR @StartDate > @EndDate BEGIN
		IF @ShortRelationshipName IS NULL
			SET  @ErrorString = '{"DataError":"From EntityRelationships - Missing ShortRelationshipName"}'
		ELSE IF @Entity1ID IS NULL
			SET  @ErrorString = '{"DataError":"From EntityRelationships - Missing Entity1ID"}'
		ELSE IF @RequestedAction IS NULL
			SET  @ErrorString = '{"DataError":"From EntityRelationships - Missing RequestedAction"}'
		ELSE IF @StartDate > @EndDate
			SET  @ErrorString = '{"DataError":"From EntityRelationships - Start date must be smaller than or equal to end date."}'
		ELSE IF @Entity2ID IS NULL
			SET  @ErrorString = '{"DataError":"From EntityRelationships - Missing Entity2ID"}'
		ELSE
			SET  @ErrorString = '{"DataError":"From EntityRelationships - Other Error"}'
	END
	ELSE BEGIN
		IF @RequestedAction = 'Create' BEGIN
			-- See if there is a relationship between the two entities whose end date is larger than
			-- the requested start date
			SELECT @ShortRelationshipName = ShortRelationshipName FROM EntityRelationships WHERE ShortRelationshipName = @ShortRelationshipName
				AND Entity1ID = @Entity1ID AND Entity2ID = @Entity2ID AND 
				(@StartDate BETWEEN StartDate AND EndDate OR @EndDate BETWEEN StartDate AND EndDate)
			-- If Rowcount is 0, insert the relationship
			IF @@ROWCOUNT = 0
			BEGIN
				SELECT @Entity1Type = EntityType FROM Entities WHERE EntityID = @Entity1ID
				IF @@ROWCOUNT = 1
				BEGIN
					SELECT @Entity2Type = EntityType FROM Entities WHERE EntityID = @Entity2ID
					IF @@ROWCOUNT = 1
					BEGIN
						SELECT @ShortRelationshipName = ShortRelationshipName FROM DefinedEntityRelationships 
						WHERE ShortRelationshipName = @ShortRelationshipName 
						AND Entity1Type = @Entity1Type AND Entity2Type = @Entity2Type
						IF @@ROWCOUNT = 1
						BEGIN
							BEGIN TRY
								BEGIN TRANSACTION WriteRelationship
									INSERT INTO EntityRelationships(ShortRelationshipName,Entity1ID,Entity2ID,StartDate,EndDate)
									VALUES (@ShortRelationshipName,@Entity1ID,@Entity2ID,@StartDate,@EndDate)

									INSERT INTO UndoEntityRelationships(ShortRelationshipName,Entity1ID,Entity2ID,
									NewEndDate,NewStartDate,ChangedBy,WhenChanged,UndoAction)
									VALUES (@ShortRelationshipName,@Entity1ID,@Entity2ID,
									@EndDate,@StartDate,@RequestorEntityID,GETDATE(),'Create')
								COMMIT TRANSACTION WriteRelationship
							END TRY
							BEGIN CATCH
								ROLLBACK TRANSACTION WriteRelationship
								SET @ErrorString = '{"DataError":"From EntityRelationships - ' + ERROR_MESSAGE() + '"}'
							END CATCH
							RETURN 0
						END
						ELSE
							SET  @ErrorString = '{"DataError":"From EntityRelationships - ' + 'A relationship type named ' + @ShortRelationshipName
								+ ' does not exist between ' + @Entity1Type + ' and ' + @Entity2Type + '"}'
					END
					ELSE
						SET  @ErrorString = '{"DataError":"From EntityRelationships - ' + 'The entity with an ID of ' + CAST(@Entity2ID AS NVARCHAR)
							+ ' does not exist.' + '"}'
				END
				ELSE
				BEGIN
					SET  @ErrorString = '{"DataError":"From EntityRelationships - ' + 'The entity with an ID of ' + CAST(@Entity1ID AS NVARCHAR)
						+ ' does not exist.' + '"}'
				END
			END
			ELSE
				SET  @ErrorString = '{"DataError":"From EntityRelationships - ' + 'Start date would overlap with a previous end date.' + '"}'
		END
		ELSE BEGIN
			-- OldStartDate and OldEndDate might be null,
			-- and the OR condition on StartDate and EndDate allows modifying one or the other
			SELECT @OldStartDate = StartDate, @OldEndDate = EndDate
			FROM EntityRelationships
			WHERE ShortRelationshipName = @ShortRelationshipName
			AND Entity1ID = @Entity1ID AND Entity2ID = @Entity2ID
			AND ((StartDate = @StartDate OR (@StartDate IS NULL AND StartDate IS NULL))
			OR (EndDate = @EndDate OR (@EndDate IS NULL AND EndDate IS NULL)))

			IF @@ROWCOUNT = 1 BEGIN
				IF @RequestedAction = 'Update' BEGIN
					BEGIN TRY
						BEGIN TRANSACTION WriteRelationship
							UPDATE EntityRelationships SET StartDate = @StartDate,EndDate = @EndDate
							WHERE ShortRelationshipName = @ShortRelationshipName
							AND Entity1ID = @Entity1ID AND Entity2ID = @Entity2ID
							AND (StartDate = @OldStartDate OR (@OldStartDate IS NULL AND StartDate IS NULL))
							AND (EndDate = @OldEndDate OR (@OldEndDate IS NULL AND EndDate IS NULL))

							INSERT INTO UndoEntityRelationships(ShortRelationshipName,Entity1ID,Entity2ID,
							NewEndDate,NewStartDate,OldEndDate,OldStartDate,ChangedBy,WhenChanged,UndoAction)
							VALUES (@ShortRelationshipName,@Entity1ID,@Entity2ID,
							@EndDate,@StartDate,@OldEndDate,@OldStartDate,@RequestorEntityID,GETDATE(),'Update')
						COMMIT TRANSACTION WriteRelationship
					END TRY
					BEGIN CATCH
						ROLLBACK TRANSACTION WriteRelationship
						SET @ErrorString = '{"DataError":"From EntityRelationships - ' + ERROR_MESSAGE() + '"}'
					END CATCH
					RETURN 0
				END
				ELSE IF @RequestedAction = 'Delete' BEGIN
					SELECT @JunkRequest = Entity1ID FROM EntityRelationships WHERE Entity1ID = @Entity1ID OR Entity2ID = @Entity1ID
					IF @@ROWCOUNT = 1
						SET @ErrorString = '{"DataError":"From EntityRelationships - Deleting the last relationship requires override"}'
					SELECT @JunkRequest = Entity2ID FROM EntityRelationships WHERE Entity1ID = @Entity2ID OR Entity2ID = @Entity2ID
					IF @@ROWCOUNT = 1
						SET @ErrorString = '{"DataError":"From EntityRelationships - Deleting the last relationship requires override"}'

					IF @ErrorString IS NULL OR (NOT @DeleteOverride IS NULL AND @DeleteOverride = 'True') BEGIN
						BEGIN TRY
							BEGIN TRANSACTION WriteRelationship
								DELETE FROM EntityRelationships
								WHERE ShortRelationshipName = @ShortRelationshipName
								AND Entity1ID = @Entity1ID AND Entity2ID = @Entity2ID
								AND (StartDate = @OldStartDate OR (@OldStartDate IS NULL AND StartDate IS NULL))
								AND (EndDate = @OldEndDate OR (@OldEndDate IS NULL AND EndDate IS NULL))

								INSERT INTO UndoEntityRelationships(ShortRelationshipName,Entity1ID,Entity2ID,
								OldEndDate,OldStartDate,ChangedBy,WhenChanged,UndoAction)
								VALUES (@ShortRelationshipName,@Entity1ID,@Entity2ID,
								@OldEndDate,@OldStartDate,@RequestorEntityID,GETDATE(),'Delete')
							COMMIT TRANSACTION WriteRelationship
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION WriteRelationship
							SET @ErrorString = '{"DataError":"From EntityRelationships - ' + ERROR_MESSAGE() + '"}'
						END CATCH
						RETURN 0
					END
				END
			END
			ELSE IF @@ROWCOUNT = 0
				SET @ErrorString = '{"DataError":"From EntityRelationships - ' + @RequestedAction + ' cannot proceed because the specified relationship does not exist.' + '"}'
			ELSE BEGIN
				SET @ErrorString = '{"DataError":"From EntityRelationships - ' + @RequestedAction + ' would effect more than one relationship, so the request has been canceled.' + '"}'
			END
		END
	END

	IF NOT @ErrorString IS NULL
	BEGIN
		DELETE FROM #JSONTable
		INSERT INTO #JSONTable
		SELECT * FROM parseJSON(@ErrorString)
		RETURN 1
	END
END
